package com.chauncy.DMS.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import util.c3p0.C3p0Utils;

import com.chauncy.DMS.bean.StuDormInfoBean;
import com.chauncy.DMS.bean.StuInfoBean;

public class UserDaoImplement implements UserDao{
	
	public StuInfoBean userLogin(StuInfoBean sib){
		// TODO Auto-generated method stub
		
		QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
		String sql ="select  *  from stu_info_table where Stu_number = ? and Stu_pwd = ? ;";
		try {
			return  queryRunner.query(
					sql,
					new BeanHandler<StuInfoBean>(StuInfoBean.class),
					sib.getStu_number(),sib.getStu_pwd());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public List<Map<String,Object>> userInfoExist(String sNub) {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select `stb`.`Dorm_number` AS `Dorm_number`," +
				"`stb`.`Dormitory_number` AS `Dormitory_number`," +
				"`stb`.`The_Class` AS `The_Class`," +
				"`stb`.`Study_major` AS `Study_major`," +
				"`stb`.`College` AS `College`," +
				"`stb`.`Telephone` AS `sTelephone`," +
				"`stb`.`Stu_sex` AS `Stu_sex`," +
				"`stb`.`Stu_name` AS `Stu_name`," +
				"`stb`.`Stu_number` AS `Stu_number`," +
				"`stb`.`Stu_id` AS `Stu_id`," +
				"`stb`.`Checkin_time` AS `Checkin_time`," +
				"`stb`.`Remarks` AS `Remarks`," +
				"`mtb`.`Manager_name` AS `Manager_name`," +
				"`mtb`.`Manager_sex` AS `Manager_sex`," +
				"`mtb`.`Telephone` AS `mTelephone` " +
				" from (`stu_dorm_info_table` `stb` join `manager_info_table` `mtb`) where (stb.Dormitory_number = `mtb`.`Manager_buildings`) AND (stb.Stu_number = '"+sNub+"');";
		List<Map<String,Object>> dormInfoDeansList = new ArrayList<Map<String,Object>>();
		 try {
			dormInfoDeansList = queryRunner.query(sql, new MapListHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		 return dormInfoDeansList;
	}

	public List<StuDormInfoBean> selectRoommate(String dormitoryNumber,String dormNumber) throws SQLException {
	
		String sql = "select  *  from  stu_dorm_info_table  WHERE   Dorm_number = '"+dormNumber+"' and  Dormitory_number = '"+dormitoryNumber+"'";

		QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
		
		return queryRunner.query(sql, new BeanListHandler<StuDormInfoBean>(StuDormInfoBean.class));
		
	}

	/**
	 * 
	 * @param args
	 * @throws SQLException
	 * 
	 * 	//	Connection connection = C3p0Utils.getConnection();
		String sql = "select Stu_name,Telephone,College,Study_major,Class  from  stu_dorm_info_table  WHERE   Dorm_number = '"+sdid.getDorm_number()+"' and  Dormitory_number = '"+sdid.getDormitory_number()+"'";
//		PreparedStatement  ps = connection.prepareStatement(sql);
//		List<StuDormInfoDean> dormInfoDeans = new ArrayList<StuDormInfoDean>();
//		ResultSet rs = ps.executeQuery(sql);
//		while(rs.next()){
//			
//			StuDormInfoDean sdi = new StuDormInfoDean();
//			sdi.setStu_name(rs.getString("Stu_name"));
//			sdi.setTelephone(rs.getString("Telephone"));
//			sdi.setCollege(rs.getString("College"));
//			sdi.setStudy_major(rs.getString("Study_major"));
//			sdi.setThe_class(rs.getString("Class"));
//			dormInfoDeans.add(sdi);
//			
//		}
//	    C3p0Utils.close(connection, ps, rs);
	 * 
	 * 
	 */
	
	//test main
	public static void main(String[] args) throws SQLException {
		UserDaoImplement implement = new UserDaoImplement();
	
		StuDormInfoBean sdid = new StuDormInfoBean();
		sdid.setDormitory_number("三栋");
		sdid.setDorm_number("302");
		String  dormitoryNumber = "三栋";
		String dormNumber = "302";
		List<StuDormInfoBean> list = implement.selectRoommate(dormitoryNumber,dormNumber);
		for(StuDormInfoBean s : list){
			System.out.println(s);
		}
		System.out.println(list.size());
	}

	





	
}
